package com.bbseguros.pesquisador.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.bbseguros.pesquisador.bean.PesquisasBean;
import com.bbseguros.pesquisador.util.ConnectDB;


/**
 * @author rrodrigues
 * 
 */
public class PesquisasDAO {

	private static Logger logger = Logger.getLogger(PesquisasDAO.class);

	private Connection conn = null;
	private static PesquisasDAO instance = null;

	private PesquisasDAO() {
		try {
			conn = ConnectDB.getConnection();
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
		}		
	}
	
	public static PesquisasDAO getInstance(){
		if(instance == null){
			instance = new PesquisasDAO();
		}
		return instance;
	}
	

	/**
	 * @param userName
	 * @param password
	 * @return
	 */
	public List<PesquisasBean> selectAll(){
		PesquisasBean pesquisa = null;
		List<PesquisasBean> pesquisas = new ArrayList<PesquisasBean>();
		
		PreparedStatement stmt = null;
		try {
			
			if (conn == null) {
				conn = ConnectDB.getConnection();
			}
			
			StringBuffer buffer = new StringBuffer();			
			buffer.append("SELECT * FROM tbl_pesquisa where id_pesquisas > 1;");
			
			stmt = conn.prepareStatement(buffer.toString());
			
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				pesquisa = new PesquisasBean();
				pesquisa.setId(rs.getInt("id_pesquisas"));
				pesquisa.setNome(rs.getString("nome"));
				pesquisa.setDescricao(rs.getString("descricao"));
				pesquisa.setTemplate(rs.getString("template"));
				pesquisa.setDataCriacao(rs.getString("data_criacao"));
				
				pesquisas.add(pesquisa);
			}
		
		} catch (SQLException e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		}catch (Exception e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		} finally {
			ConnectDB.cleanUp(stmt);
		}
		return pesquisas;
	}

	public PesquisasBean selectPesquisaById(int pesqId){
		PesquisasBean pesquisa = null;
		
		PreparedStatement stmt = null;
		try {
			
			if (conn == null) {
				conn = ConnectDB.getConnection();
			}
			
			StringBuffer buffer = new StringBuffer();			
			buffer.append("SELECT * FROM tbl_pesquisa WHERE id_pesquisas = ?;");
			
			stmt = conn.prepareStatement(buffer.toString());
			stmt.setInt(1, pesqId);
			
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				pesquisa = new PesquisasBean();
				pesquisa.setId(rs.getInt("id_pesquisas"));
				pesquisa.setNome(rs.getString("nome"));
				pesquisa.setDescricao(rs.getString("descricao"));
				pesquisa.setTemplate(rs.getString("template"));
				pesquisa.setDataCriacao(rs.getString("data_criacao"));
			}
		
		} catch (SQLException e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		}catch (Exception e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		} finally {
			ConnectDB.cleanUp(stmt);
		}
		return pesquisa;
	}
	
	/**
	 * @param userName
	 * @param password
	 * @return
	 */
	public List<PesquisasBean> selectByIds(int[] ids){
		PesquisasBean pesquisa = null;
		List<PesquisasBean> pesquisas = new ArrayList<PesquisasBean>();
		
		PreparedStatement stmt = null;
		try {
			
			if (conn == null) {
				conn = ConnectDB.getConnection();
			}
			
			StringBuffer buffer = new StringBuffer();			
			buffer.append("SELECT * FROM tbl_pesquisa WHERE ");
			
			for (int i = 0; i < ids.length; i++) {
				if(i<(ids.length-1)) {
					buffer.append("id_pesquisas = ? OR ");
				} else {
					buffer.append("id_pesquisas = ?;");
				}
			}
			
			logger.debug("STATEMENT: '"+buffer.toString()+"'");
			
			stmt = conn.prepareStatement(buffer.toString());
			
			for (int i = 0; i < ids.length; i++) {
				stmt.setInt(i+1, ids[i]);
			}
			
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				pesquisa = new PesquisasBean();
				pesquisa.setId(rs.getInt("id_pesquisas"));
				pesquisa.setNome(rs.getString("nome"));
				pesquisa.setDescricao(rs.getString("descricao"));
				pesquisa.setTemplate(rs.getString("template"));
				pesquisa.setDataCriacao(rs.getString("data_criacao"));
				
				pesquisas.add(pesquisa);
			}
		
		} catch (SQLException e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		}catch (Exception e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		} finally {
			ConnectDB.cleanUp(stmt);
		}
		return pesquisas;
	}

	/**
	 * @param bean
	 * @return
	 */
	public boolean insertPesquisa(PesquisasBean bean) {
		boolean toReturn = false;
		PreparedStatement stmt = null;
		try {
			
			if (conn == null) {
				conn = ConnectDB.getConnection();
			}
			int idPesq = getPesquisasId();
			
			StringBuffer buffer = new StringBuffer();			
			buffer.append("INSERT INTO tbl_pesquisa (id_pesquisas, nome, descricao, template, data_criacao)");
			buffer.append("VALUES (?,?,?,?,(SELECT NOW()))");
			
			stmt = conn.prepareStatement(buffer.toString());
			stmt.setInt(1, idPesq);
			stmt.setString(2, bean.getNome());
			stmt.setString(3, bean.getDescricao());
			stmt.setString(4, bean.getTemplate());

			int ret = stmt.executeUpdate();	
			if(ret>0) {
				toReturn = true;
			}
			logger.debug("toReturn: "+toReturn);
		} catch (SQLException e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		}catch (Exception e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		} finally {
			ConnectDB.cleanUp(stmt);
		}
		return toReturn;
	}
	
	public boolean updatePesquisa(PesquisasBean bean) {
		boolean toReturn = false;
		PreparedStatement stmt = null;
		try {
			
			if (conn == null) {
				conn = ConnectDB.getConnection();
			}
			StringBuffer buffer = new StringBuffer();			
			buffer.append("UPDATE tbl_pesquisa SET nome= ?, descricao= ?, template= ? ");
			buffer.append("WHERE id_pesquisas=?");
			
			stmt = conn.prepareStatement(buffer.toString());
			stmt.setString(1, bean.getNome());
			stmt.setString(2, bean.getDescricao());
			stmt.setString(3, bean.getTemplate());
			stmt.setInt(4, bean.getId());

			int ret = stmt.executeUpdate();	
			if(ret>0) {
				toReturn = true;
			}
			logger.debug("toReturn: "+toReturn);
		} catch (SQLException e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		}catch (Exception e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		} finally {
			ConnectDB.cleanUp(stmt);
		}
		return toReturn;
	}
	
	public boolean deletedPesquisaById(int pesqId){
		boolean toReturn = false;
		
		PreparedStatement stmt = null;
		try {
			
			if (conn == null) {
				conn = ConnectDB.getConnection();
			}
			
			StringBuffer buffer = new StringBuffer();			
			buffer.append("DELETE FROM tbl_pesquisa WHERE id_pesquisas = ?;");
			
			stmt = conn.prepareStatement(buffer.toString());
			stmt.setInt(1, pesqId);
			
			int ret = stmt.executeUpdate();	
			if(ret>0) {
				toReturn = true;
			}
			logger.debug("toReturn: "+toReturn);
		
		} catch (SQLException e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		}catch (Exception e) {
			conn = null;
			logger.error(e.getMessage(), e);
			
		} finally {
			ConnectDB.cleanUp(stmt);
		}
		return toReturn;
	}	
	
	public int getPesquisasId(){
		if (conn == null) {
			return 0;
		}
		Statement stmt = null;
		int returnId = 0;

		try {
			returnId = ConnectDB.getSequenceMySql(conn, "seq_pesquisa");

		} catch (Exception e) {
			conn = null;
			logger.error(e.getMessage(), e);
		}  finally {
			ConnectDB.cleanUp(stmt);
		}
		return returnId;
	}
}
